const { Pool } = require('pg');
const { v4: uuidv4 } = require('uuid');

// 从环境变量获取数据库配置
const pool = new Pool({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD
});

/**
 * 创建新订单
 * @param {Object} orderData - 订单数据
 * @returns {Promise<Object>} 订单信息
 */
async function createOrder(orderData) {
  const { items, totalPrice, userInfo } = orderData;
  
  if (!items || items.length === 0) {
    throw new Error('订单商品不能为空');
  }
  
  if (!userInfo || !userInfo.name || !userInfo.phone) {
    throw new Error('用户信息不完整');
  }
  
  const client = await pool.connect();
  try {
    const orderId = uuidv4();
    const query = `
      INSERT INTO orders (id, items, total_price, user_info, status, created_at)
      VALUES ($1, $2, $3, $4, $5, NOW())
      RETURNING *
    `;
    
    const values = [
      orderId,
      JSON.stringify(items),
      totalPrice,
      JSON.stringify(userInfo),
      'pending'
    ];
    
    const result = await client.query(query, values);
    return result.rows[0];
  } finally {
    client.release();
  }
}

/**
 * 获取订单详情
 * @param {string} orderId - 订单ID
 * @returns {Promise<Object>} 订单详情
 */
async function getOrderById(orderId) {
  const client = await pool.connect();
  try {
    const query = 'SELECT * FROM orders WHERE id = $1';
    const result = await client.query(query, [orderId]);
    
    if (result.rows.length === 0) {
      throw new Error('订单不存在');
    }
    
    return result.rows[0];
  } finally {
    client.release();
  }
}

/**
 * 获取用户订单列表
 * @param {string} phone - 用户手机号
 * @param {number} limit - 每页数量
 * @param {number} offset - 偏移量
 * @returns {Promise<Array>} 订单列表
 */
async function getOrdersByPhone(phone, limit = 10, offset = 0) {
  const client = await pool.connect();
  try {
    const query = `
      SELECT * FROM orders 
      WHERE user_info->>'phone' = $1 
      ORDER BY created_at DESC 
      LIMIT $2 OFFSET $3
    `;
    
    const result = await client.query(query, [phone, limit, offset]);
    return result.rows;
  } finally {
    client.release();
  }
}

/**
 * 更新订单状态
 * @param {string} orderId - 订单ID
 * @param {string} status - 新状态
 * @returns {Promise<Object>} 更新后的订单
 */
async function updateOrderStatus(orderId, status) {
  const validStatuses = ['pending', 'confirmed', 'preparing', 'delivering', 'completed', 'cancelled'];
  
  if (!validStatuses.includes(status)) {
    throw new Error('无效的订单状态');
  }
  
  const client = await pool.connect();
  try {
    const query = `
      UPDATE orders 
      SET status = $1, updated_at = NOW() 
      WHERE id = $2 
      RETURNING *
    `;
    
    const result = await client.query(query, [status, orderId]);
    
    if (result.rows.length === 0) {
      throw new Error('订单不存在');
    }
    
    return result.rows[0];
  } finally {
    client.release();
  }
}

/**
 * 计算订单总价
 * @param {Array} items - 商品列表
 * @returns {Promise<number>} 总价
 */
async function calculateTotalPrice(items) {
  if (!items || items.length === 0) {
    return 0;
  }
  
  const client = await pool.connect();
  try {
    const dishIds = items.map(item => item.dishId);
    const query = 'SELECT id, price FROM dishes WHERE id = ANY($1)';
    const result = await client.query(query, [dishIds]);
    
    const priceMap = new Map(result.rows.map(row => [row.id, parseFloat(row.price)]));
    
    let total = 0;
    for (const item of items) {
      const price = priceMap.get(item.dishId);
      if (price) {
        total += price * item.quantity;
      }
    }
    
    return parseFloat(total.toFixed(2));
  } finally {
    client.release();
  }
}

/**
 * 获取所有订单（管理端）
 * @param {Object} filters - 过滤条件
 * @returns {Promise<Array>} 订单列表
 */
async function getAllOrders(filters = {}) {
  const client = await pool.connect();
  try {
    let query = 'SELECT * FROM orders WHERE 1=1';
    const params = [];
    let paramIndex = 1;
    
    if (filters.status) {
      query += ` AND status = $${paramIndex}`;
      params.push(filters.status);
      paramIndex++;
    }
    
    if (filters.startDate) {
      query += ` AND created_at >= $${paramIndex}`;
      params.push(filters.startDate);
      paramIndex++;
    }
    
    if (filters.endDate) {
      query += ` AND created_at <= $${paramIndex}`;
      params.push(filters.endDate);
      paramIndex++;
    }
    
    query += ' ORDER BY created_at DESC';
    
    if (filters.limit) {
      query += ` LIMIT $${paramIndex}`;
      params.push(filters.limit);
      paramIndex++;
    }
    
    if (filters.offset) {
      query += ` OFFSET $${paramIndex}`;
      params.push(filters.offset);
    }
    
    const result = await client.query(query, params);
    return result.rows;
  } finally {
    client.release();
  }
}

/**
 * 删除订单（仅测试环境）
 * @param {string} orderId - 订单ID
 * @returns {Promise<boolean>} 是否删除成功
 */
async function deleteOrder(orderId) {
  const client = await pool.connect();
  try {
    const query = 'DELETE FROM orders WHERE id = $1';
    const result = await client.query(query, [orderId]);
    return result.rowCount > 0;
  } finally {
    client.release();
  }
}

module.exports = {
  createOrder,
  getOrderById,
  getOrdersByPhone,
  updateOrderStatus,
  calculateTotalPrice,
  getAllOrders,
  deleteOrder
};